package MyCustomTable;

import MyUtil.*;

import java.io.*;
import java.net.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.sql.*;
import java.awt.*;
import java.awt.event.*;
import java.util.Date;
import java.util.List;
import javax.swing.event.*;
import javax.swing.*;
import javax.swing.table.*;

import static MyUtil.CSVUtil.searchSavePath;

public class Pay_List_DynamicQuery extends JFrame{
    //基本设置
    private String[] headString={"销售序号","客户编号","书籍编号","购买数量","购买总价格","采购时间"};
    private ResultSet rs;
    private Set<Date> dateSet = new TreeSet<>();
    private Set<String> monthSet = new TreeSet<>();
    private Set<String> quarterSet = new TreeSet<>();
    private CardLayout cardLayout = new CardLayout();
    private Connection conn;
    private JScrollPane jScrollPane = new JScrollPane();
    //按钮
    private JButton jb1 = new JButton("按天查询");
    private JButton jb2 = new JButton("按月查询");
    private JButton jb3 = new JButton("按季度查询");

    private JButton day_output = new JButton("天表导出");
    private JButton month_output = new JButton("月表导出");
    private JButton quarter_output = new JButton("季度表导出");
    //控件
    private JPanel day_panel = new JPanel();
    private JPanel month_panel = new JPanel();
    private JPanel quarter_panel = new JPanel();
    private JPanel total_panel = new MyPanel("/picture/5.jpg");
    private JPanel left_panel = new JPanel();
    private JPanel white_panel = new JPanel();
    //JComboBox
    private JComboBox<java.util.Date> dayJComboBox;
    private JComboBox<String> monthJComboBox;
    private JComboBox<String> quarterJComboBox;

    public Pay_List_DynamicQuery(JFrame parent) throws SQLException {
        super("分时查询销售");
        conn = DataBaseUtil.GetConnection();
        this.setBounds(300,200,1000,700);
        this.setLayout(new BorderLayout());
        this.add(left_panel,BorderLayout.WEST);
        this.add(total_panel,BorderLayout.CENTER);
        //左边框
        left_panel.setLayout(new GridLayout(3,1));
        left_panel.add(jb1);
        left_panel.add(jb2);
        left_panel.add(jb3);
        //右边框
        total_panel.setLayout(cardLayout);
        total_panel.add(white_panel,"layout0");
        total_panel.add(day_panel,"layout1");
        total_panel.add(month_panel,"layout2");
        total_panel.add(quarter_panel,"layout3");
        //JPanel布局·部分
        day_panel.setLayout(new BorderLayout());
        month_panel.setLayout(new BorderLayout());
        quarter_panel.setLayout(new BorderLayout());
        //date集的获取
        rs = DataBaseUtil.produceResultSet("select Pdate from Pay_List");
        while(rs.next()){
            java.util.Date d1 = rs.getDate("Pdate");
            String s1[] = d1.toString().split("-");
            //月份
            monthSet.add(s1[0]+"-"+s1[1]);
            //季度
            int n;
            n = Integer.parseInt(s1[1]);
            if(n>0&&n<=3){
                quarterSet.add(s1[0]+"年第一季度");
            }else if(n>3&&n<=6){
                quarterSet.add(s1[0]+"年第二季度");
            }else if(n>6&&n<=9){
                quarterSet.add(s1[0]+"年第三季度");
            }else if(n>9&&n<=12){
                quarterSet.add(s1[0]+"年第四季度");
            }
            //日期添加
            dateSet.add(d1);
        }
        //绘制组件
        makeDayTimeJPanel();
        makeMonthTimeJpanel();
        makeQuarterTimeJpanel();
        //按钮功能
        button_FUNCTION();

    }

    public void button_FUNCTION(){
        jb1.addActionListener((e)->{
            try {
                dayJComboBox_FUNCTION();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            cardLayout.show(total_panel,"layout1");
        });
        jb2.addActionListener((e)->{
            try {
                monthJComboBox_FUNCTION();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            cardLayout.show(total_panel,"layout2");
        });
        jb3.addActionListener((e)->{
            try {
                quarterJComboBox_FUNCTION();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            cardLayout.show(total_panel,"layout3");
        });
        day_output.addActionListener((e)->{
            try {
                day_Button_FUNCTION();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        });
        month_output.addActionListener((e)->{
            try {
                month_Button_FUNCTION();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        });
        quarter_output.addActionListener((e) ->{
            try {
                quarter_Button_FUNCTION();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        });
    }//建立按钮

    //按天导出组件功能函数
    public void makeDayTimeJPanel(){
        //JComboBox的添加
        dayJComboBox = new JComboBox<>();
        for (Date d1:dateSet){
            dayJComboBox.addItem(d1);
        }
        day_panel.add(dayJComboBox,BorderLayout.PAGE_START);


        day_panel.add(day_output,BorderLayout.PAGE_END);
        dayJComboBox.addActionListener((e)->{
            try {
                dayJComboBox_FUNCTION();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        });
    }
    public void dayJComboBox_FUNCTION() throws SQLException {
        java.util.Date d1 = (java.util.Date)dayJComboBox.getSelectedItem();
        java.sql.Date date1 = new java.sql.Date(d1.getTime());
        String sql = "select * from Pay_List where Pdate = ?";
        /*
        PreparedStatement p1 = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
        p1.setDate(1, (java.sql.Date) d1);
        rs = p1.executeQuery();*/
        rs = DataBaseUtil.prepareResultSet(sql,date1);
        day_panel.remove(jScrollPane);
        month_panel.remove(jScrollPane);
        quarter_panel.remove(jScrollPane);

        jScrollPane= JScrollUtil.getCustomJScrollPane(rs);
        day_panel.add(jScrollPane,BorderLayout.CENTER);
        cardLayout.show(total_panel,"layout0");
        cardLayout.show(total_panel,"layout1");
    }
    public void day_Button_FUNCTION() throws SQLException {
        java.util.Date d1 = (java.util.Date)dayJComboBox.getSelectedItem();
        java.sql.Date date1 = new java.sql.Date(d1.getTime());
        String sql = "select * from Pay_List where Pdate = ?";
        rs = DataBaseUtil.prepareResultSet(sql,date1);
        List<Object[]> list = ObjectListUtil.getDataList(rs);
        CSVUtil.searchSavePath(this,list,headString,d1.toString()+"销售表");
    }
    //按月导出组件功能函数
    public void makeMonthTimeJpanel(){
        monthJComboBox = new JComboBox<String>();
        for (String s1:monthSet){
            monthJComboBox.addItem(s1);
        }
        month_panel.add(monthJComboBox,BorderLayout.PAGE_START);

        month_panel.add(month_output,BorderLayout.PAGE_END);
        monthJComboBox.addActionListener((e)->{
            try {
                monthJComboBox_FUNCTION();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        });
    }
    public void monthJComboBox_FUNCTION() throws SQLException {
        String d1 = (String) monthJComboBox.getSelectedItem();
        String sql = "select * from Pay_List where TO_CHAR(Pdate,'yyyy-mm-dd') like "+"'"+d1+"%"+"'";
        rs = DataBaseUtil.produceResultSet(sql);

        day_panel.remove(jScrollPane);
        month_panel.remove(jScrollPane);
        quarter_panel.remove(jScrollPane);

        jScrollPane= JScrollUtil.getCustomJScrollPane(rs);
        month_panel.add(jScrollPane,BorderLayout.CENTER);
        cardLayout.show(total_panel,"layout0");
        cardLayout.show(total_panel,"layout2");
    }
    public void month_Button_FUNCTION() throws SQLException {
        String d1 = (String) monthJComboBox.getSelectedItem();
        String sql = "select * from Pay_List where TO_CHAR(Pdate,'yyyy-mm-dd') like "+"'"+d1+"%"+"'";
        List<Object[]> list = ObjectListUtil.getDataList(sql);
        CSVUtil.searchSavePath(this,list,headString,d1.toString()+"月销售表");
    }
    //按季度导出组件功能函数
    public void makeQuarterTimeJpanel(){
        //添加JComboBox
        quarterJComboBox = new JComboBox<String>();
        for (String s1:quarterSet){
            quarterJComboBox.addItem(s1);
        }
        quarter_panel.add(quarterJComboBox,BorderLayout.PAGE_START);

        quarter_panel.add(quarter_output,BorderLayout.PAGE_END);
        quarterJComboBox.addActionListener((e)->{
            try {
                quarterJComboBox_FUNCTION();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        });
    }
    public void quarterJComboBox_FUNCTION() throws SQLException {
        String d1 = (String) quarterJComboBox.getSelectedItem();
        String sql = null;

        if(d1.endsWith("年第一季度")){
            String mysql = d1.replaceFirst("年第一季度","");
            sql = "select * from Pay_List where TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"01-%' or " +
                    "TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"02-%' or "+
                    "TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"03-%'";

        }
        else if(d1.endsWith("年第二季度")){
            String mysql = d1.replaceFirst("年第二季度","");
            sql = "select * from Pay_List where TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"04-%' or " +
                    "TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"05-%' or "+
                    "TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"06-%'";
        }else if(d1.endsWith("年第三季度")){
            String mysql = d1.replaceFirst("年第三季度","");
            sql = "select * from Pay_List where TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"07-%' or " +
                    "TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"08-%' or "+
                    "TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"09-%'";
        }
        else if(d1.endsWith("年第四季度")){
            String mysql = d1.replaceFirst("年第四季度","");
            sql = "select * from Pay_List where TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"10-%' or " +
                    "TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"11-%' or "+
                    "TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"12-%'";
        }


        day_panel.remove(jScrollPane);
        month_panel.remove(jScrollPane);
        quarter_panel.remove(jScrollPane);

        rs = DataBaseUtil.produceResultSet(sql);
        jScrollPane= JScrollUtil.getCustomJScrollPane(rs);
        quarter_panel.add(jScrollPane,BorderLayout.CENTER);
        cardLayout.show(total_panel,"layout0");
        cardLayout.show(total_panel,"layout3");
    }
    public void quarter_Button_FUNCTION() throws SQLException {
        String d1 = (String) quarterJComboBox.getSelectedItem();
        String sql = null;

        if(d1.endsWith("年第一季度")){
            String mysql = d1.replaceFirst("年第一季度","");
            sql = "select * from Pay_List where TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"01-%' or " +
                    "TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"02-%' or "+
                    "TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"03-%'";

        }
        else if(d1.endsWith("年第二季度")){
            String mysql = d1.replaceFirst("年第二季度","");
            sql = "select * from Pay_List where TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"04-%' or " +
                    "TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"05-%' or "+
                    "TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"06-%'";
        }else if(d1.endsWith("年第三季度")){
            String mysql = d1.replaceFirst("年第三季度","");
            sql = "select * from Pay_List where TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"07-%' or " +
                    "TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"08-%' or "+
                    "TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"09-%'";
        }
        else if(d1.endsWith("年第四季度")){
            String mysql = d1.replaceFirst("年第四季度","");
            sql = "select * from Pay_List where TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"10-%' or " +
                    "TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"11-%' or "+
                    "TO_CHAR(Pdate,'yyyy-mm-dd') like '"+mysql+"-"+"12-%'";
        }
        List<Object[]> list = ObjectListUtil.getDataList(sql);
        CSVUtil.searchSavePath(this,list,headString,d1.toString()+"销售表");
    }
}
